package com.eascs.paymentdetail.service;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.admin.o2o.internal.entity.LoanapplyExpand;
import com.eascs.admin.o2o.internal.entity.LoanapplyExpandDetail;
import com.eascs.admin.o2o.internal.service.LoanapplyExpandDetailService;
import com.eascs.admin.o2o.internal.service.LoanapplyExpandService;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.paymentdetail.dao.O2oPaymentDetailDao;
import com.eascs.paymentdetail.dao.O2oRedBlueDao;
import com.eascs.paymentdetail.dao.OaRedBlueDao;
import com.eascs.paymentdetail.entity.O2oPaymentDetailEntity;
import com.eascs.paymentdetail.entity.OaPaymentLoanExpand;
import com.eascs.paymentdetail.vo.O2oPaymentDetailPageVo;
import com.eascs.paymentdetail.vo.O2oPaymentDetailVo;

/**
 * @ClassName: O2oPaymentDetailDao.java
 * @Description:
 *               <p>
 *               财务收付明细 Service
 *               </p>
 * @author CXW
 * @date 2016年9月9日 下午2:38:48
 * @version V1.0
 */
@Service
public class O2oPaymentDetailService {
	
	private static Logger logger = LoggerFactory.getLogger(O2oPaymentDetailService.class);

	@Autowired
	O2oPaymentDetailDao paymentDetailDao;
	
	@Autowired
	O2oRedBlueDao o2oRedBlueDao;
	
	@Autowired
	OaRedBlueDao oaRedBlueDao;
	@Autowired
	private LoanapplyExpandDetailService loanapplyExpandDetailService;
	@Autowired
	private LoanapplyExpandService loanapplyExpandService;

	/**
	 * @Description:
	 *               <p>
	 *               分页查询明细表
	 *               </p>
	 * @author CXW
	 * @date 2016年9月9日 下午4:25:36
	 * @version V1.0
	 * @param
	 * @return PageResult<Object[]>
	 */
	public PageResult<Object[]> findByPage(O2oPaymentDetailPageVo vo) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, vo.getPageSize(), vo.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		if(StringUtil.isEmpty(vo.getFlag())){
			querySql.append(" SELECT OPD.BILL_CODE,OPD.PAYMENT_TYPE,OPD.SUBJECT_TYPE, ");
			querySql.append(" OPD.AMOUNT_MONEY,OPD.RECORD_TIME,OPD.UPDATE_TIME,OPD.OPERATION_NAME,OPD.HAPPEN_SOURCE,OPD.REMARK ");
			querySql.append(" FROM O2O_PAYMENT_DETAIL OPD WHERE 1=1 ");
		}else {
			//导出excel语句
			querySql.append(" SELECT OPD.BILL_CODE, CASE WHEN OPD.PAYMENT_TYPE = 'F' THEN '支出'  WHEN OPD.PAYMENT_TYPE = 'XS' THEN '虚收'  WHEN OPD.PAYMENT_TYPE = 'XF' THEN '虚退' ELSE '付款' END AS PAYMENT_TYPE,");
			querySql.append(" item.item_name AS SUBJECT_TYPE,OPD.AMOUNT_MONEY, OPD.RECORD_TIME, OPD.UPDATE_TIME, OPD.OPERATION_NAME,");
			querySql.append(" OPD.HAPPEN_SOURCE, OPD.REMARK FROM O2O_PAYMENT_DETAIL OPD LEFT JOIN ");
			querySql.append(" pub_dditem_o2o item ON OPD.subject_type = item.item_code AND dd_no = 101 AND item.is_lock = 'N'");
			querySql.append("  WHERE 1 = 1 ");
		}
		
		if (!StringUtil.isEmpty(vo.getBillCode())) {
			querySql.append(" AND OPD.BILL_CODE LIKE ? ");
			sqlPageQuery.addLikeParam(vo.getBillCode());
		}
		if (!StringUtil.isEmpty(vo.getLoanapplyNo())) {
			querySql.append(" AND OPD.LOANAPPLY_NO = ? ");
			sqlPageQuery.addParam(vo.getLoanapplyNo());
		}
		if (!StringUtil.isEmpty(vo.getOperUserName())) {
			querySql.append(" AND OPD.OPERATION_NAME LIKE ? ");
			sqlPageQuery.addLikeParam(vo.getOperUserName());
		}
		if (!StringUtil.isEmpty(vo.getSubject())) {
			querySql.append(" AND OPD.SUBJECT_TYPE = ? ");
			sqlPageQuery.addParam(vo.getSubject());
		}
		if (!StringUtil.isEmpty(vo.getPaymentType())) {
			querySql.append(" AND OPD.PAYMENT_TYPE = ? ");
			sqlPageQuery.addParam(vo.getPaymentType());
		}
		if (null != vo.getStartDate()) {
			querySql.append(" AND OPD.UPDATE_TIME >= ? ");
			sqlPageQuery.addParam(vo.getStartDate());
		}
		if (null != vo.getEndDate()) {
			querySql.append(" AND OPD.UPDATE_TIME < ? ");
			sqlPageQuery.addParam(vo.getEndDate());
		}
		querySql.append(" ORDER BY OPD.UPDATE_TIME DESC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return paymentDetailDao.findDataWithPage(sqlPageQuery);
	}

	/**
	 * @Description:
	 * 				<p>
	 *               插入财务收付信息表
	 *               </p>
	 * @author CXW
	 * @date 2016年9月9日 下午2:57:39
	 * @version V1.0
	 * @param o2oPaymentDetailEntity
	 * @return int
	 */
	public int saveEntity(O2oPaymentDetailEntity o2oPaymentDetailEntity) {
		return paymentDetailDao.insert(o2oPaymentDetailEntity);
	}

	/**
	 * 划拨单子保存明细
	 * 
	 * @param o2oPaymentDetailEntity
	 * @return
	 */
	@Transactional("o2oTransactionManager")
	public void saveEntitys(O2oPaymentDetailEntity o2oPaymentDetail1, O2oPaymentDetailEntity o2oPaymentDetail2,
			O2oPaymentDetailEntity o2oPaymentDetail3, O2oPaymentDetailEntity o2oPaymentDetail4,
			O2oPaymentDetailEntity o2oPaymentDetail5) {
		paymentDetailDao.insert(o2oPaymentDetail1);
		paymentDetailDao.insert(o2oPaymentDetail2);
		paymentDetailDao.insert(o2oPaymentDetail3);
		if(o2oPaymentDetail4!=null)
			paymentDetailDao.insert(o2oPaymentDetail4);
		if(o2oPaymentDetail5!=null)
		paymentDetailDao.insert(o2oPaymentDetail5);
	}
	/**
	 * 没有划拨保存明细
	 * @param o2oPaymentDetailEntity
	 * @return
	 */
	@Transactional("o2oTransactionManager")
	public void saveNewEntity(LoanapplyExpand loanapplyExpand,O2oPaymentDetailEntity o2oPaymentDetailEntity1,O2oPaymentDetailEntity o2oPaymentDetailEntity2,O2oPaymentDetailEntity o2oPaymentDetailEntity3) {
		 paymentDetailDao.insert(o2oPaymentDetailEntity1);
		 paymentDetailDao.insert(o2oPaymentDetailEntity2);
		 paymentDetailDao.insert(o2oPaymentDetailEntity3);
		 LoanapplyExpand loanapplyExpand1=loanapplyExpandService.queryLoanapplyNo(loanapplyExpand.getLoanapply_no());
		 LoanapplyExpandDetail loanapplyExpandDetail=loanapplyExpandDetailService.queryDetail(loanapplyExpand.getId());
		 if(loanapplyExpandDetail !=null){
				loanapplyExpand.setTransferDeposit(loanapplyExpandDetail.getTransferDeposit());
				loanapplyExpand.setTransferNum(loanapplyExpandDetail.getTransferNum());
				loanapplyExpand.setPaidDeposite(loanapplyExpandDetail.getPaidDeposite());
				loanapplyExpand.setPaidManger(loanapplyExpandDetail.getPaidManger());
				loanapplyExpand.setPaidRisk(loanapplyExpandDetail.getPaidRisk());
				BigDecimal tranDeposit=loanapplyExpandDetail.getTransferDeposit()==null?BigDecimal.ZERO:loanapplyExpandDetail.getTransferDeposit();
				loanapplyExpand.setRedDeposite(loanapplyExpandDetail.getRedDeposite().add(loanapplyExpand1.getRedDeposite()==null?BigDecimal.ZERO:loanapplyExpand1.getRedDeposite()).add(tranDeposit));
				loanapplyExpand.setRedManger(loanapplyExpandDetail.getRedManger().add(loanapplyExpand1.getRedManger()==null?BigDecimal.ZERO:loanapplyExpand1.getRedManger()));
				loanapplyExpand.setRedRisk(loanapplyExpandDetail.getRedRisk().add(loanapplyExpand1.getRedRisk()==null?BigDecimal.ZERO:loanapplyExpand1.getRedRisk()));
				logger.info("LoanapplyExpand : not is transfer bond net receipts:[{}]", loanapplyExpand.getRedRisk());
				logger.info("LoanapplyExpand1 : not is transfer bond net receipts:[{}]", loanapplyExpand1.getRedRisk());
				logger.info("LoanapplyExpandDetail : not is transfer bond net receipts:[{}]", loanapplyExpandDetail.getRedRisk());
				loanapplyExpand.setPayRemark(loanapplyExpandDetail.getPayRemark());
				loanapplyExpand.setSubmitDate(loanapplyExpandDetail.getSubmitDate());
				loanapplyExpand.setPayDate(loanapplyExpandDetail.getPayDate());
				loanapplyExpand.setUpdator(loanapplyExpandDetail.getUpdator());
				logger.info("临时表添加到loanapplyExpand主表：RedDeposite{}RedManger:{}RedRisk:{}", loanapplyExpand.getRedDeposite(),loanapplyExpand.getRedManger(),loanapplyExpand.getRedRisk());
				loanapplyExpandService.updateById(loanapplyExpand);
			}
	}

	/**
	 * @Description:
	 *               <p>
	 *               更新财务收付信息表
	 *               </p>
	 * @author CXW
	 * @date 2016年9月9日 下午2:57:39
	 * @version V1.0
	 * @param o2oPaymentDetailEntity
	 * @return int
	 */
	@Transactional("o2oTransactionManager")
	public int updateEntityById(O2oPaymentDetailEntity o2oPaymentDetailEntity) {
		return paymentDetailDao.updateById(o2oPaymentDetailEntity);
	}

	/**
	 * @Description
	 *              <p>
	 *              详情和审批页面中的【保证金、管理费和风险补偿金、代偿详情展示】内容调整为 <br>
	 *              查询方法
	 *              </p>
	 * @author CXW
	 * @date 2016年9月9日 下午4:27:43
	 * @version V1.0
	 * @param loanapplyNo
	 * @return O2oPaymentDetailVo
	 */
	public O2oPaymentDetailVo findByBillCodeOrLoanapplyNo(String loanapplyNo) {
		StringBuffer sql = new StringBuffer();
		sql.append(
				"SELECT FORMAT(ifnull(ole.depositrate,0)*100,2) DEPOSITRATE,FORMAT(ifnull(ole.managefeerate,0)*100,2) MANAGEFEERATE,");
		sql.append("FORMAT(OLE.RISK*100,4) RISK,OLE.REC_DEPOSITE AS BOND_TOTAL,");
		sql.append("OLE.REC_MANGER AS MANAGER_TOTAL,OLE.REC_RISK AS RISK_TOTAL,OLE.REC_DEPOSITE,");
		sql.append(
				"OLE.REC_MANGER,OLE.REC_RISK,OLE.RED_DEPOSITE AS RED_DEPOSITE,");
		sql.append("IFNULL(OLE.RED_MANGER,0) AS RED_MANGER,IFNULL(OLE.RED_RISK,0) AS RED_RISK,");
		sql.append("IFNULL(OLE.RETA_DEPOSITE,0) AS RETA_DEPOSITE,IFNULL(OLE.RETA_MANAGE,0) AS RETA_MANAGE,");
		sql.append("IFNULL(OLE.RETA_RISK,0) AS RETA_RISK,");
		sql.append(
				"(IFNULL(OLE.RED_DEPOSITE,0)-IFNULL(OLE.RETA_DEPOSITE,0)) AS RETN_DEPOSITE,");
		sql.append("(IFNULL(OLE.RED_MANGER,0)-IFNULL(OLE.RETA_MANAGE,0)) AS RETN_MANAGE,");
		sql.append(
				"(IFNULL(OLE.RED_RISK,0)-IFNULL(OLE.RETA_RISK,0)) AS RETN_RISK,OLE.PAY_NAME,OLE.SUBMIT_DATE,OLE.remark ");
		sql.append(" FROM O2O_LOANAPPLY_EXPAND OLE WHERE OLE.LOANAPPLY_NO=? ");
		return paymentDetailDao.findEntity(sql.toString(), O2oPaymentDetailVo.class, loanapplyNo);
	}
	public List<O2oPaymentDetailEntity> findBillCode(String billCode){
		return paymentDetailDao.queryBillCode(billCode);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               	根据SQL更新数据
	 *               </p>
	 * @author CXW
	 * @date 2016年10月10日 下午3:23:08
	 * @version V1.0
	 */
	@Transactional("o2oTransactionManager")
	public int updateBySql(String sql,Object ...params) {
		if(StringUtil.isEmpty(sql)){
			return 0;
		}
		return paymentDetailDao.execute(sql, params);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               联想
	 *               </p>
	 * @author CXW
	 * @date 2016年10月12日 上午10:36:48
	 * @version V1.0
	 */
	public List<Object[]> getBilcodeList(String billCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ole.bill_code as bill_code_no,ole.bill_code as bill_name ");
		sql.append(" from o2o_loanapply ole where ole.bill_code like ?  LIMIT 10 ");

		return paymentDetailDao.findObjectsList(sql.toString(), "%" + billCode + "%");
	}
	
	/**
	 * 
	 * @Description:
	 *               <p>
	 *               红冲蓝补修改新系统数据库
	 *               </p>
	 * @author CXW
	 * @date 2016年10月12日 下午3:52:06
	 * @version V1.0
	 */
	@Transactional("o2oTransactionManager")
	public int updateNewSysEntityById(O2oPaymentDetailEntity o2oPaymentDetailEntity,LoanapplyExpand loanapplyExpand) {
		paymentDetailDao.updateById(loanapplyExpand);
		return paymentDetailDao.insert(o2oPaymentDetailEntity);
	}
	
	/**
	 * 
	 * @Description:
	 *               <p>
	 *               红冲蓝补修改老系统数据库
	 *               </p>
	 * @author CXW
	 * @date 2016年10月12日 下午4:31:06
	 * @version V1.0
	 */
	@Transactional("o2oTransactionManager")
	public int updateOaSysEntityById(O2oPaymentDetailEntity o2oPaymentDetailEntity,OaPaymentLoanExpand loanapplyExpand) {
		oaRedBlueDao.updateById(loanapplyExpand);
		return paymentDetailDao.insert(o2oPaymentDetailEntity);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据贷款编号查询
	 *               </p>
	 * @author CXW
	 * @date 2016年10月13日 上午9:11:12
	 * @version V1.0
	 */
	public OaPaymentLoanExpand getEntityById(String billCode){
		return oaRedBlueDao.getById(OaPaymentLoanExpand.class, billCode);
	}
}
